﻿Public Class ReservationAdd
    Dim no As Int16 = 0
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        MessageBox.Show("Testing")
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Close()
    End Sub

    Private Sub btnnewcus_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnnewcus.Click
        Dim frm As New ClientAdd
        frm.ShowDialog()
    End Sub

    Private Sub ReservationAdd_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        UI2.FormatDataGrid(DataGridView2)
        UI2.FormatDataGrid(dgvavailable)
        txtreNo.Enabled = False
        txtreNo.Text = NoGenerater.GenerateNewReservationNo
        txtprice.Enabled = False
        loadrtype()
        loadroom()
        loadroomavalable()
        loadclient()
        loadretype()
        txtRoom.Enabled = False
    End Sub
    Private Sub loadretype()
        cboretype.DisplayMember = "ReservationType"
        cboretype.ValueMember = "ReservationTypeId"
        cboretype.DataSource = cmd.ExecuteDataTable("SELECT ReservationTypeId,ReservationType FROM ReservationType WHERE Active=1")
    End Sub
    Private Sub loadclient()
        cboclient.DisplayMember = "ClientName"
        cboclient.ValueMember = "ClientId"
        cboclient.DataSource = cmd.ExecuteDataTable("SELECT ClientName,ClientId FROM Client Where Active=1")
    End Sub
    Private Sub loadroomavalable()
        cmd.Parameters("@start") = dtpstart.Value
        cmd.Parameters("@end") = dtpend.Value
        Dim sql = "SELECT r.RoomNo,r.RoomPrice FROM Room as R Inner Join Room as S on R.RoomId=S.RoomId where R.RoomId not in (Select rd.RoomId from Reservation r inner join ReservationDetail rd on rd.ReservationId=r.ReservationId where r.Active =1 and R.DateIn < @end AND r.DateOut > @start ) and S.RoomId not in (SELECT cd.RoomId FROM Checkin c INNER JOIN CheckInDetail cd on cd.CheckInId=c.CheckInId where c.active = 1 and c.DateIn < @end AND c.DateOut > @start) "
        dgvavailable.DataSource = cmd.ExecuteDataTable(sql)
    End Sub

    Private Sub loadroom()
        cmd.Parameters("@rtype") = cborType.SelectedValue
        cboRoom.DisplayMember = "RoomNo"
        cboRoom.ValueMember = "RoomId"
        cboRoom.DataSource = cmd.ExecuteDataTable("SELECT roomid,roomno FROM room WHERE roomtypeid=@rtype AND active=1")
    End Sub
    Private Sub loadrtype()
        cborType.DisplayMember = "roomtype"
        cborType.ValueMember = "roomtypeid"
        cborType.DataSource = cmd.ExecuteDataTable("SELECT roomtypeid,roomtype FROM roomtype WHERE active=1")
    End Sub
    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

        Dim i = Me.DataGridView2.Rows.Add()
        Dim row = Me.DataGridView2.Rows(i)
        row.Cells("room_id").Value = cboRoom.SelectedValue
        row.Cells("rtype").Value = cborType.Text
        row.Cells("rprice").Value = txtprice.Text
        row.Cells("room_no").Value = cboRoom.Text
        'row.Cells("rNo").Value = no
        no += 1
        txtRoom.Text = no
    End Sub

    Private Sub loadprice()
        Dim dt As DataTable
        cmd.Parameters("@id") = cboRoom.SelectedValue
        dt = cmd.ExecuteDataTable("SELECT RoomPrice FROM Room WHERE roomid=@id")
        Dim r = dt.Rows(0)
        txtprice.Text = r("roomprice")
    End Sub

    Private Sub cborType_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cborType.SelectedIndexChanged
        cboRoom.Text = ""
        loadroom()
    End Sub

    Private Sub cboRoom_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboRoom.SelectedIndexChanged
        loadprice()
    End Sub

    Private Sub DateTimePicker2_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dtpend.ValueChanged
        loadroomavalable()
    End Sub

    Private Sub dtpstart_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dtpstart.ValueChanged
        loadroomavalable()
    End Sub

   
    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        Dim row As DataGridViewRow
        row = DataGridView2.SelectedRows(0)
        Me.DataGridView2.Rows.Remove(row)
        no -= 1
        txtRoom.Text = no
    End Sub

    Private Sub RemoveToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RemoveToolStripMenuItem.Click
        RemoveLine()
        no -= 1
        txtRoom.Text = no
    End Sub
    Private Sub RemoveLine()
        If Me.DataGridView2.SelectedRows.Count = 0 Then
            Exit Sub
        End If
        Dim row = Me.DataGridView2.SelectedRows(0)
        Me.DataGridView2.Rows.Remove(row)
    End Sub

    Private Sub RemoveAllToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RemoveAllToolStripMenuItem.Click
        DataGridView2.Rows.Clear()
        no = 0
        txtRoom.Text = 0
    End Sub

    Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click

        Try
            cmd.Begin()
            'cmd.Parameters("@PurchaseId") = Me.txtPurchaseId.Text
            cmd.Parameters("@ReNo") = Me.txtreNo.Text
            cmd.Parameters("@retype") = Me.cboretype.SelectedValue
            cmd.Parameters("@DateIn") = Me.dtpIn.Value
            cmd.Parameters("@DateOut") = Me.dtpOut.Value
            cmd.Parameters("@ClientId") = Me.cboclient.SelectedValue
            cmd.Parameters("@Totalroom") = Me.txtRoom.Text
            cmd.Parameters("@empid") = 1
            cmd.Parameters("@Redate") = Now.Date


            'If Me.txtPurchaseId.Text = "" Then
            cmd.ExecuteNonQuery("INSERT INTO Reservation VALUES(@ReNo,@Retype,@ClientId,@ReDate,@DateIn,@DateOut,@empId,@Totalroom,1);")
            'Else
            'cmd.ExecuteNonQuery("UPDATE Purchase SET PurchaseNo=@PurchaseNo,PurchaseDate=@PurchaseDate,VendorId=@VendorId,StoreId=@StoreId,Subtotal=@Subtotal,Discount=@Discount,Total=@Total WHERE PurchaseId=@PurchaseId;")
            'End If

            Dim id = cmd.ExecuteScalar("SELECT Max(ReservationId) FROM Reservation;")
            For Each row As DataGridViewRow In Me.DataGridView2.Rows
                ' add purchase detail
                'cmd.Parameters("@PurchaseDetailId") = row.Cells("PurchaseDetailId").Value
                cmd.Parameters("@ReId") = id
                cmd.Parameters("@RoomId") = row.Cells("Room_Id").Value

                'If row.Cells("PurchaseDetailId").Value Is Nothing Then
                cmd.ExecuteNonQuery("INSERT INTO ReservationDetail VALUES(@ReId,@RoomId,1);")
                'Else
                'cmd.ExecuteNonQuery("UPDATE PurchaseDetail SET ProductId=@ProductId,Qty=@Qty,Price=@Price,Amount=@Amount WHERE PurchaseDetailId=@PurchaseDetailId")
                'End If
            Next
            MessageBox.Show("Sucess")
            cmd.Commit()
            Me.DialogResult = Windows.Forms.DialogResult.OK
        Catch ex As Exception
            cmd.Rollback()
            MsgBox(ex.Message)
        End Try
    End Sub
End Class
